package com.pet.dao;

import com.pet.bean.Petstore;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class PetstoreDao {
    //宠物商店登录的操作
    public Petstore login_Petstore(Connection con, String name, String pw) throws SQLException {
        String sql = "select * from petstore where name=? and password=?";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setString(1, name);
        ps.setString(2, pw);
        ResultSet resultSet = ps.executeQuery();
        while (resultSet.next()) {
            int store_id = resultSet.getInt("store_id");
            String name1 = resultSet.getString("name");
            String password = resultSet.getString("password");
            double wealth = resultSet.getDouble("wealth");
            int status = resultSet.getInt("status");
            Petstore petstore = new Petstore(store_id, name1, password, wealth, status);
            return petstore;
        }
        return null;
    }
    //宠物商店注册的操作
    public boolean insert_Petstore(Connection con, Petstore Petstore) throws SQLException {
        String sql = "insert into Petstore(name,password,wealth) values (?,?,?)";
        PreparedStatement preparedStatement = con.prepareStatement(sql);
        preparedStatement.setString(1, Petstore.getName());
        preparedStatement.setString(2, Petstore.getPassword());
        preparedStatement.setDouble(3, Petstore.getWealth());
        int i = preparedStatement.executeUpdate();
        if (i > 0) {
            return true;
        }
        return false;
    }
    //宠物商店修改的操作
    public boolean update_Petstore(Connection con, Petstore Petstore) throws SQLException {
        String sql = "update Petstore set name=? password=? wealth=? where Petstore_id=?";
        PreparedStatement preparedStatement = con.prepareStatement(sql);
        preparedStatement.setString(1, Petstore.getName());
        preparedStatement.setString(2, Petstore.getPassword());
        preparedStatement.setDouble(3, Petstore.getWealth());
        preparedStatement.setInt(4, Petstore.getStore_id());
        int i = preparedStatement.executeUpdate();
        if (i > 0) {
            return true;
        }
        return false;
    }
    //宠物商店名字
    public boolean update_PetstoreName(Connection con, int id,String name) throws SQLException {
        String sql = "update petstore set name=? where store_id=?";
        PreparedStatement preparedStatement = con.prepareStatement(sql);
        preparedStatement.setString(1, name);
        preparedStatement.setInt(2, id);
        int i = preparedStatement.executeUpdate();
        if (i > 0) {
            return true;
        }
        return false;
    }
    //宠物商店密码
    public boolean update_PetstorePw(Connection con, int id,String password) throws SQLException {
        String sql = "update petstore set password=? where store_id=?";
        PreparedStatement preparedStatement = con.prepareStatement(sql);
        preparedStatement.setString(1, password);
        preparedStatement.setInt(2, id);
        int i = preparedStatement.executeUpdate();
        if (i > 0) {
            return true;
        }
        return false;
    }
    //宠物商店财富
    public boolean update_PetstoreWea(Connection con, int id,double wealth) throws SQLException {
        String sql = "update petstore set wealth=? where store_id=?";
        PreparedStatement preparedStatement = con.prepareStatement(sql);
        preparedStatement.setDouble(1, wealth);
        preparedStatement.setInt(2, id);
        int i = preparedStatement.executeUpdate();
        if (i > 0) {
            return true;
        }
        return false;
    }
    //宠物商店账号删除注销的操作
    public boolean delete_Petstore(Connection con, int id) throws SQLException {
        String sql = "update petstore set status=0 where store_id=?";
        PreparedStatement preparedStatement = con.prepareStatement(sql);
        preparedStatement.setInt(1, id);
        int i = preparedStatement.executeUpdate();
        if (i > 0) {
            return true;
        }
        return false;
    }
    //修改宠物商店金额的操作（卖出宠物）
    public void update_PetstoreWealth1(Connection con, int store_id, double wealth) throws SQLException {
        String sql = "update petstore set wealth=wealth+? where store_id=?";
        PreparedStatement preparedStatement = con.prepareStatement(sql);
        preparedStatement.setDouble(1, wealth);
        preparedStatement.setInt(2, store_id);
        preparedStatement.executeUpdate();
    }
    //修改宠物商店金额的操作（买下宠物）
    public void update_PetstoreWealth2(Connection con, int store_id, double wealth) throws SQLException {
        String sql = "update petstore set wealth=wealth-? where store_id=?";
        PreparedStatement preparedStatement = con.prepareStatement(sql);
        preparedStatement.setDouble(1, wealth);
        preparedStatement.setInt(2, store_id);
        preparedStatement.executeUpdate();
    }
    //查看商店列表
    public List<Petstore> selectAllStore(Connection con) throws SQLException {
        List<Petstore> list = new ArrayList<>();
        String sql = "select * from petstore where status=1";
        PreparedStatement ps = con.prepareStatement(sql);
        ResultSet resultSet = ps.executeQuery();
        while (resultSet.next()){
            int store_id = resultSet.getInt("store_id");
            String name = resultSet.getString("name");
            String password = resultSet.getString("password");
            double wealth = resultSet.getDouble("wealth");
            int status = resultSet.getInt("status");
            Petstore petstore = new Petstore(store_id,name,password,wealth,status);
            list.add(petstore);
        }
        return list;
    }
    //根据store_id查询商店
    public Petstore select_PetstoreByID(Connection con,int s_id) throws SQLException {
        String sql = "select * from petstore where store_id=?";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setInt(1,s_id);
        ResultSet resultSet = ps.executeQuery();
        while (resultSet.next()){
            int store_id = resultSet.getInt("store_id");
            String name = resultSet.getString("name");
            String password = resultSet.getString("password");
            double wealth = resultSet.getDouble("wealth");
            int status = resultSet.getInt("status");
            Petstore petstore = new Petstore(store_id,name,password,wealth,status);
            return petstore;
        }
        return null;
    }
    //查看商店余额
    public double selectStoreWealth(Connection con,int s_id) throws SQLException {
        String sql = "select wealth from petstore where store_id=?";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setInt(1,s_id);
        ResultSet resultSet = ps.executeQuery();
        while (resultSet.next()){
            double wealth = resultSet.getDouble("wealth");
            return wealth;
        }
        return 0;
    }
    //开宠物商店
    public boolean openPetStore(Connection con,Petstore petstore) throws SQLException {
        String sql = "insert into petstore(name,password,wealth) values(?,?,?)";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setString(1,petstore.getName());
        ps.setString(2,petstore.getPassword());
        ps.setDouble(3,petstore.getWealth());
        int i = ps.executeUpdate();
        if (i>0){
            return true;
        }else {
            return false;
        }
    }
}
